import Router from "koa-router";
import { authMiddleware } from "../../../middleware/auth";
import { pool } from "../../../config/database";
import { RowDataPacket, ResultSetHeader } from "mysql2";
import { 
  checkAdminPermission, 
  getPaginationParams, 
  createPaginatedResponse, 
  getSearchParams,
  handleError,
  successResponse,
  validateId,
  validateBatchIds
} from "../common";

const router = new Router({ prefix: "/api/admin/ip-logs" });

// 获取IP访问日志列表 - 支持搜索和分页
router.get("/", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { page, limit, offset } = getPaginationParams(ctx);
    const { keyword, method, status, startDate, endDate } = getSearchParams(ctx);

    // 构建查询条件
    let whereClause = "WHERE 1=1";
    const params: any[] = [];

    if (keyword) {
      whereClause += " AND (ip_address LIKE ? OR user_agent LIKE ? OR url LIKE ?)";
      const searchTerm = `%${keyword}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }

    if (method) {
      whereClause += " AND method = ?";
      params.push(method);
    }

    if (status) {
      whereClause += " AND status_code = ?";
      params.push(parseInt(status as string));
    }

    if (startDate) {
      whereClause += " AND created_at >= ?";
      params.push(startDate);
    }

    if (endDate) {
      whereClause += " AND created_at <= ?";
      params.push(endDate + ' 23:59:59');
    }

    // 获取总数
    const [countResult] = await pool.execute(
      `SELECT COUNT(*) as total FROM access_logs ${whereClause}`,
      params
    ) as [RowDataPacket[], any];
    const total = countResult[0].total;

    // 获取IP访问日志列表
    const [logs] = await pool.execute(`
      SELECT 
        id, ip_address, method, url, status_code, response_time,
        user_agent, referer, user_id, created_at
      FROM access_logs 
      ${whereClause}
      ORDER BY created_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `, params) as [RowDataPacket[], any];

    // 获取用户信息（如果有用户ID）
    const userIds = logs.filter(log => log.user_id).map(log => log.user_id);
    let userInfo: any = {};

    if (userIds.length > 0) {
      const [users] = await pool.execute(`
        SELECT id, username, email FROM users 
        WHERE id IN (${userIds.map(() => '?').join(',')})
      `, userIds) as [RowDataPacket[], any];
      
      userInfo = users.reduce((acc, user) => {
        acc[user.id] = { username: user.username, email: user.email };
        return acc;
      }, {} as any);
    }

    // 组合数据
    const enrichedLogs = logs.map(log => ({
      ...log,
      userInfo: log.user_id ? userInfo[log.user_id] : null
    }));

    const response = createPaginatedResponse(enrichedLogs, total, page, limit);
    successResponse(ctx, response, "获取IP访问日志成功");

  } catch (error) {
    handleError(ctx, error, "获取IP访问日志失败");
  }
});

// 获取IP统计信息
router.get("/statistics/ip", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { page, limit, offset } = getPaginationParams(ctx);
    const { startDate, endDate } = getSearchParams(ctx);

    // 构建时间条件
    let timeClause = "";
    const params: any[] = [];

    if (startDate) {
      timeClause += " AND created_at >= ?";
      params.push(startDate);
    }

    if (endDate) {
      timeClause += " AND created_at <= ?";
      params.push(endDate + ' 23:59:59');
    }

    // 获取IP统计总数
    const [countResult] = await pool.execute(`
      SELECT COUNT(DISTINCT ip_address) as total 
      FROM access_logs 
      WHERE 1=1 ${timeClause}
    `, params) as [RowDataPacket[], any];
    const total = countResult[0].total;

    // 获取IP访问统计
    const [ipStats] = await pool.execute(`
      SELECT 
        ip_address,
        COUNT(*) as request_count,
        COUNT(DISTINCT user_id) as unique_users,
        COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_count,
        AVG(response_time) as avg_response_time,
        MIN(created_at) as first_access,
        MAX(created_at) as last_access,
        COUNT(DISTINCT DATE(created_at)) as active_days
      FROM access_logs 
      WHERE 1=1 ${timeClause}
      GROUP BY ip_address
      ORDER BY request_count DESC
      LIMIT ${limit} OFFSET ${offset}
    `, params) as [RowDataPacket[], any];

    // 获取每个IP的地理位置信息（如果有的话）
    const ips = ipStats.map(stat => stat.ip_address);
    let locationInfo: any = {};

    if (ips.length > 0) {
      try {
        const [locations] = await pool.execute(`
          SELECT ip_address, country, region, city 
          FROM ip_locations 
          WHERE ip_address IN (${ips.map(() => '?').join(',')})
        `, ips) as [RowDataPacket[], any];
        
        locationInfo = locations.reduce((acc, loc) => {
          acc[loc.ip_address] = {
            country: loc.country,
            region: loc.region,
            city: loc.city
          };
          return acc;
        }, {} as any);
      } catch (e) {
        console.warn('获取IP地理位置失败:', e);
      }
    }

    // 组合数据
    const enrichedStats = ipStats.map(stat => ({
      ...stat,
      location: locationInfo[stat.ip_address] || null,
      errorRate: ((stat.error_count / stat.request_count) * 100).toFixed(2) + '%'
    }));

    const response = createPaginatedResponse(enrichedStats, total, page, limit);
    successResponse(ctx, response, "获取IP统计成功");

  } catch (error) {
    handleError(ctx, error, "获取IP统计失败");
  }
});

// 获取访问趋势统计
router.get("/statistics/trends", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { days = 7 } = ctx.query;
    const daysNum = Math.min(parseInt(days as string) || 7, 90); // 最多90天

    // 按小时统计（最近24小时）
    const [hourlyStats] = await pool.execute(`
      SELECT 
        HOUR(created_at) as hour,
        COUNT(*) as request_count,
        COUNT(DISTINCT ip_address) as unique_ips,
        COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_count
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
      GROUP BY HOUR(created_at)
      ORDER BY hour
    `) as [RowDataPacket[], any];

    // 按日统计
    const [dailyStats] = await pool.execute(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as request_count,
        COUNT(DISTINCT ip_address) as unique_ips,
        COUNT(DISTINCT user_id) as unique_users,
        COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_count,
        AVG(response_time) as avg_response_time
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `, [daysNum]) as [RowDataPacket[], any];

    // 方法统计
    const [methodStats] = await pool.execute(`
      SELECT 
        method,
        COUNT(*) as count,
        AVG(response_time) as avg_response_time
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      GROUP BY method
      ORDER BY count DESC
    `, [daysNum]) as [RowDataPacket[], any];

    // 状态码统计
    const [statusStats] = await pool.execute(`
      SELECT 
        status_code,
        COUNT(*) as count
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      GROUP BY status_code
      ORDER BY count DESC
    `, [daysNum]) as [RowDataPacket[], any];

    // 热门页面统计
    const [topPages] = await pool.execute(`
      SELECT 
        url,
        COUNT(*) as visit_count,
        COUNT(DISTINCT ip_address) as unique_visitors,
        AVG(response_time) as avg_response_time
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
        AND method = 'GET'
      GROUP BY url
      ORDER BY visit_count DESC
      LIMIT 20
    `, [daysNum]) as [RowDataPacket[], any];

    successResponse(ctx, {
      period: `${daysNum} days`,
      hourly: hourlyStats,
      daily: dailyStats,
      methods: methodStats,
      statusCodes: statusStats,
      topPages
    }, "获取访问趋势统计成功");

  } catch (error) {
    handleError(ctx, error, "获取访问趋势统计失败");
  }
});

// 获取可疑IP检测
router.get("/security/suspicious", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { hours = 24 } = ctx.query;
    const hoursNum = Math.min(parseInt(hours as string) || 24, 168); // 最多7天

    // 检测高频访问IP
    const [highFrequencyIPs] = await pool.execute(`
      SELECT 
        ip_address,
        COUNT(*) as request_count,
        COUNT(CASE WHEN status_code = 404 THEN 1 END) as not_found_count,
        COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_count,
        COUNT(DISTINCT url) as unique_urls,
        MIN(created_at) as first_access,
        MAX(created_at) as last_access
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)
      GROUP BY ip_address
      HAVING request_count > 100 OR not_found_count > 20
      ORDER BY request_count DESC
      LIMIT 50
    `, [hoursNum]) as [RowDataPacket[], any];

    // 检测扫描行为（大量404请求）
    const [scanningIPs] = await pool.execute(`
      SELECT 
        ip_address,
        COUNT(*) as total_requests,
        COUNT(CASE WHEN status_code = 404 THEN 1 END) as not_found_count,
        (COUNT(CASE WHEN status_code = 404 THEN 1 END) / COUNT(*) * 100) as not_found_rate
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)
      GROUP BY ip_address
      HAVING not_found_count > 10 AND not_found_rate > 50
      ORDER BY not_found_count DESC
      LIMIT 30
    `, [hoursNum]) as [RowDataPacket[], any];

    // 检测异常User-Agent
    const [suspiciousUserAgents] = await pool.execute(`
      SELECT 
        user_agent,
        COUNT(DISTINCT ip_address) as ip_count,
        COUNT(*) as request_count
      FROM access_logs 
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)
        AND (user_agent IS NULL OR user_agent = '' OR user_agent LIKE '%bot%' OR user_agent LIKE '%spider%')
      GROUP BY user_agent
      ORDER BY request_count DESC
      LIMIT 20
    `, [hoursNum]) as [RowDataPacket[], any];

    // 地理位置异常（如果有地理位置数据）
    let geoAnomalies: any[] = [];
    try {
      const [geoStats] = await pool.execute(`
        SELECT 
          al.ip_address,
          il.country,
          COUNT(*) as request_count
        FROM access_logs al
        LEFT JOIN ip_locations il ON al.ip_address = il.ip_address
        WHERE al.created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)
          AND il.country IS NOT NULL
        GROUP BY al.ip_address, il.country
        HAVING request_count > 50
        ORDER BY request_count DESC
        LIMIT 20
      `, [hoursNum]) as [RowDataPacket[], any];
      geoAnomalies = geoStats;
    } catch (e) {
      console.warn('获取地理位置异常失败:', e);
    }

    successResponse(ctx, {
      period: `${hoursNum} hours`,
      highFrequencyIPs,
      scanningIPs,
      suspiciousUserAgents,
      geoAnomalies,
      summary: {
        totalSuspiciousIPs: highFrequencyIPs.length,
        totalScanningIPs: scanningIPs.length,
        totalSuspiciousUserAgents: suspiciousUserAgents.length
      }
    }, "获取可疑IP检测结果成功");

  } catch (error) {
    handleError(ctx, error, "获取可疑IP检测失败");
  }
});

// 添加IP到黑名单
router.post("/blacklist", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { ip_address, reason, expires_at } = ctx.request.body as {
      ip_address: string;
      reason?: string;
      expires_at?: string;
    };

    if (!ip_address) {
      ctx.status = 400;
      ctx.body = { success: false, message: "IP地址为必填项" };
      return;
    }

    // 验证IP地址格式
    const ipRegex = /^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$/;
    if (!ipRegex.test(ip_address)) {
      ctx.status = 400;
      ctx.body = { success: false, message: "无效的IP地址格式" };
      return;
    }

    // 检查IP是否已在黑名单中
    const [existing] = await pool.execute(
      "SELECT id FROM ip_blacklist WHERE ip_address = ? AND (expires_at IS NULL OR expires_at > NOW())",
      [ip_address]
    ) as [RowDataPacket[], any];

    if (existing.length > 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "IP地址已在黑名单中" };
      return;
    }

    // 添加到黑名单
    const [result] = await pool.execute(
      "INSERT INTO ip_blacklist (ip_address, reason, expires_at, created_by) VALUES (?, ?, ?, ?)",
      [ip_address, reason, expires_at, ctx.state.user.id]
    ) as [ResultSetHeader, any];

    successResponse(ctx, { 
      id: result.insertId,
      ip_address,
      reason,
      expires_at 
    }, "IP地址已添加到黑名单");

  } catch (error) {
    handleError(ctx, error, "添加IP黑名单失败");
  }
});

// 获取黑名单列表
router.get("/blacklist", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { page, limit, offset } = getPaginationParams(ctx);
    const { keyword, active } = getSearchParams(ctx);

    // 构建查询条件
    let whereClause = "WHERE 1=1";
    const params: any[] = [];

    if (keyword) {
      whereClause += " AND (ib.ip_address LIKE ? OR ib.reason LIKE ?)";
      const searchTerm = `%${keyword}%`;
      params.push(searchTerm, searchTerm);
    }

    if (active !== undefined) {
      if (active === 'true') {
        whereClause += " AND (ib.expires_at IS NULL OR ib.expires_at > NOW())";
      } else if (active === 'false') {
        whereClause += " AND ib.expires_at IS NOT NULL AND ib.expires_at <= NOW()";
      }
    }

    // 获取总数
    const [countResult] = await pool.execute(
      `SELECT COUNT(*) as total FROM ip_blacklist ib ${whereClause}`,
      params
    ) as [RowDataPacket[], any];
    const total = countResult[0].total;

    // 获取黑名单列表
    const [blacklist] = await pool.execute(`
      SELECT 
        ib.*,
        u.username as created_by_username
      FROM ip_blacklist ib
      LEFT JOIN users u ON ib.created_by = u.id
      ${whereClause}
      ORDER BY ib.created_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `, params) as [RowDataPacket[], any];

    const response = createPaginatedResponse(blacklist, total, page, limit);
    successResponse(ctx, response, "获取IP黑名单成功");

  } catch (error) {
    handleError(ctx, error, "获取IP黑名单失败");
  }
});

// 从黑名单移除IP
router.delete("/blacklist/:id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const id = validateId(ctx);
    if (id === null) return;

    // 检查黑名单记录是否存在
    const [blacklist] = await pool.execute(
      "SELECT ip_address FROM ip_blacklist WHERE id = ?",
      [id]
    ) as [RowDataPacket[], any];

    if (blacklist.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "黑名单记录不存在" };
      return;
    }

    // 删除黑名单记录
    await pool.execute("DELETE FROM ip_blacklist WHERE id = ?", [id]);

    successResponse(ctx, { 
      id, 
      ip_address: blacklist[0].ip_address 
    }, "IP地址已从黑名单移除");

  } catch (error) {
    handleError(ctx, error, "移除IP黑名单失败");
  }
});

// 清理旧日志
router.post("/cleanup", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { days = 90 } = ctx.request.body as { days?: number };

    if (days <= 0 || days > 365) {
      ctx.status = 400;
      ctx.body = { success: false, message: "天数必须在1-365之间" };
      return;
    }

    // 删除指定天数前的访问日志
    const [result] = await pool.execute(
      "DELETE FROM access_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)",
      [days]
    ) as [ResultSetHeader, any];

    successResponse(ctx, { 
      cleanedDays: days,
      deletedLogs: result.affectedRows 
    }, `清理${days}天前的访问日志成功，删除${result.affectedRows}条记录`);

  } catch (error) {
    handleError(ctx, error, "清理访问日志失败");
  }
});

export default router; 